/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package telsoft.cms_admin.data;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.cms_admin.entity.ETT_FormServices;
import telsoft.cms_admin.entity.ETT_FormSystem;
import telsoft.sql.Database;
import telsoft.util.DateUtil;

/**
 *
 * @author hoannc
 */
public class DATA_FormServices implements Serializable {

    private String SysId;
    private SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");

    public List getUser2Group() throws Exception {
        List returnData = new ArrayList<ETT_FormSystem>();
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String strSQL = " select a.system_id,a.system_code,a.system_name,a.status from sdp_systems a order by a.system_id, a.system_name ";
        try {
//            open();
            // Get User list
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_FormSystem tree = new ETT_FormSystem();
                tree.setSYS_ID(rs.getString(1));
                tree.setSYS_CODE(rs.getString(2));
                tree.setSYS_NAME(rs.getString(3));
                tree.setSTATUS(rs.getString(4));
                returnData.add(tree);
            }
            Database.closeObject(rs);
            Database.closeObject(stmt);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    public List loadDetailProduct(ETT_FormSystem ETT_FormSystem) throws Exception {
        List returnData = new ArrayList<ETT_FormServices>();
        Connection cn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String strSQL = "";
        try {
//            open();
            strSQL = " SELECT a.service_id, a.sdp_service_id, a.service_name, TO_CHAR(a.created_time,'dd/MM/yyyy hh24:mi:ss'), "
                    + " TO_CHAR(a.effective_time,'dd/MM/yyyy hh24:mi:ss'), TO_CHAR(a.expiry_time,'dd/MM/yyyy hh24:mi:ss'), a.service_number, a.system_id, a.status "
                    + " FROM sdp_services a "
                    + " where a.system_id = ? "
                    + " order by a.sdp_service_id,a.service_name ";
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            ///////////
            stmt.setString(1, ETT_FormSystem.getSYS_ID());

            rs = stmt.executeQuery();
            while (rs.next()) {
                ETT_FormServices dtDetail = new ETT_FormServices();
                dtDetail.setSER_ID(rs.getString(1));
                dtDetail.setSER_CODE(rs.getString(2));
                dtDetail.setSER_NAME(rs.getString(3));
                if (rs.getString(4) != null) {
                    dtDetail.setCREATED_TIME(DateUtil.toDate(rs.getString(4), "dd/MM/yyyy HH:mm:ss"));
                }
                dtDetail.setEFFECTIVE_TIME(DateUtil.toDate(rs.getString(5), "dd/MM/yyyy HH:mm:ss"));
                dtDetail.setEXPIRY_TIME(DateUtil.toDate(rs.getString(6), "dd/MM/yyyy HH:mm:ss"));
                dtDetail.setStrCREATED_TIME(rs.getString(4));
                dtDetail.setStrEFFECTIVE_TIME(rs.getString(5));
                dtDetail.setStrEXPIRY_TIME(rs.getString(6));
                dtDetail.setSER_NUMBER(rs.getString(7));
                dtDetail.setSYS_ID(rs.getString(8));
                dtDetail.setSTATUS(rs.getString(9));
                returnData.add(dtDetail);
            }
        } catch (Exception ex) {
            ex.fillInStackTrace();
        } finally {
//            close();
            rs.close();
            stmt.close();
            Database.closeObject(cn);
        }
        return returnData;
    }

    /* public List search(ETT_FormSystem ETT_FormSystem, ETT_FormServices ETT_FormServices) throws Exception {
     List returnData = new ArrayList<ETT_FormServices>();
     Connection cn = null;
     PreparedStatement stmt = null;
     ResultSet rs = null;
     String strSQL = "";
     //        try {
     //            open();
     strSQL = " SELECT product_id, "
     + " sdp_product_code, "
     + " product_name, "
     + " welcome_message, "
     + " farewell_message, "
     + " service_number, "
     + " register_keyword, "
     + " cancel_keyword, "
     + " TO_CHAR(created_time,'dd/MM/yyyy hh24:mi:ss'),"
     + " TO_CHAR(effective_time,'dd/MM/yyyy hh24:mi:ss'),"
     + " TO_CHAR(expiry_time,'dd/MM/yyyy hh24:mi:ss'),"
     + " content_builder_class_id,"
     + " content_builder_type,"
     + " content_mode,"
     + " charging_mode,"
     + " charging_class_id,"
     + " status, service_id"
     + " FROM sdp_products"
     + " WHERE service_id = ? ";

     Vector vtParam = new Vector();
     vtParam.addElement(ETT_FormSystem.getID());
     if (!ETT_FormServices.getSDP_PRODUCT_CODE().equals("")) {
     strSQL += " and  sdp_product_code like upper('%'||?||'%') ";
     vtParam.addElement(ETT_FormServices.getSDP_PRODUCT_CODE());
     }
     if (!ETT_FormServices.getPRODUCT_NAME().equals("")) {
     strSQL += " and product_name like upper('%'||?||'%') ";
     vtParam.addElement(ETT_FormServices.getPRODUCT_NAME());
     }
     if (!ETT_FormServices.getSTATUS().equals("")) {
     strSQL += " and status = ? ";
     vtParam.addElement(ETT_FormServices.getSTATUS());
     }
     strSQL += " ORDER BY sdp_product_code, product_name";

     try {
     //            open();
     cn = AppServer.getConnection();
     stmt = cn.prepareStatement(strSQL);
     for (int i = 0; i < vtParam.size(); i++) {
     stmt.setString(i + 1, vtParam.elementAt(i).toString());
     }
     rs = stmt.executeQuery();
     while (rs.next()) {
     ETT_FormServices dtDetail = new ETT_FormServices();
     dtDetail.setPRODUCT_ID(rs.getString(1));
     dtDetail.setSDP_PRODUCT_CODE(rs.getString(2));
     dtDetail.setPRODUCT_NAME(rs.getString(3));
     dtDetail.setWELCOME_MESSAGE(rs.getString(4));
     dtDetail.setFAREWELL_MESSAGE(rs.getString(5));
     dtDetail.setSERVICE_NUMBER(rs.getString(6));
     dtDetail.setREGISTER_KEYWORD(rs.getString(7));
     dtDetail.setCANCEL_KEYWORD(rs.getString(8));
     dtDetail.setCREATED_TIME(DateUtil.toDate(rs.getString(9), "dd/MM/yyyy HH:mm:ss"));
     dtDetail.setEFFECTIVE_TIME(DateUtil.toDate(rs.getString(10), "dd/MM/yyyy HH:mm:ss"));
     dtDetail.setEXPIRY_TIME(DateUtil.toDate(rs.getString(11), "dd/MM/yyyy HH:mm:ss"));
     dtDetail.setStrCREATED_TIME(rs.getString(9));
     dtDetail.setStrEFFECTIVE_TIME(rs.getString(10));
     dtDetail.setStrEXPIRY_TIME(rs.getString(11));
     dtDetail.setCONTENT_BUILDER_CLASS_ID(rs.getString(12));
     dtDetail.setCONTENT_BUILDER_TYPE(rs.getString(13));
     dtDetail.setCONTENT_MODE(rs.getString(14));
     dtDetail.setCHARGING_MODE(rs.getString(15));
     dtDetail.setCHARGING_CLASS_ID(rs.getString(16));
     dtDetail.setSTATUS(rs.getString(17));
     dtDetail.setSERVICE_ID(rs.getString(18));
     returnData.add(dtDetail);
     }
     } catch (Exception ex) {
     ex.printStackTrace();
     } finally {
     rs.close();
     stmt.close();
     //            close();
     }
     return returnData;
     }*/
    public ETT_FormServices insert(ETT_FormServices ETT_FormServices) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
//        ResultSet rs = null;
        String strSQL = " insert into sdp_services a(a.service_id, a.sdp_service_id, a.service_name, a.created_time, "
                + " a.effective_time, a.expiry_time, a.service_number, a.system_id, a.status) "
                + " values(?,?,?,to_date(?,'dd/mm/yyyy hh24:mi:ss'),to_date(?,'dd/mm/yyyy hh24:mi:ss'),to_date(?,'dd/mm/yyyy hh24:mi:ss'),?,?,?) ";
        try {
//            open();
            cn = AppServer.getConnection();
            SysId = Database.getSequenceValue(cn, "sdp_service_seq");
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, SysId);
            stmt.setString(2, ETT_FormServices.getSER_CODE());
            stmt.setString(3, ETT_FormServices.getSER_NAME());
            stmt.setString(4, df.format(ETT_FormServices.getCREATED_TIME()));
            stmt.setString(5, df.format(ETT_FormServices.getEFFECTIVE_TIME()));
            stmt.setString(6, df.format(ETT_FormServices.getEXPIRY_TIME()));
            stmt.setString(7, ETT_FormServices.getSER_NUMBER());
            stmt.setString(8, ETT_FormServices.getSYS_ID());
            stmt.setString(9, ETT_FormServices.getSTATUS());
            stmt.execute();
            stmt.close();
            ETT_FormServices.setSER_ID(SysId);
            ETT_FormServices.setStrCREATED_TIME(df.format(ETT_FormServices.getCREATED_TIME()));
            ETT_FormServices.setStrEFFECTIVE_TIME(df.format(ETT_FormServices.getEFFECTIVE_TIME()));
            ETT_FormServices.setStrEXPIRY_TIME(df.format(ETT_FormServices.getEXPIRY_TIME()));
            /////////INSERT CONTENT MODE
//            strSQL = " insert into sdp_system_content_mode (system_id, content_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < ContentMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, ContentMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
//            cn.setAutoCommit(true);
//            close(stmt);
            /////////INSERT CHARGING MODE
//            strSQL = " insert into sdp_system_charging_mode (system_id, charging_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < CharMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, CharMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
//            cn.setAutoCommit(true);
//            close(stmt);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
//            close(rs);
//            close(stmt);
//            close();
            Database.closeObject(cn);
        }
        return ETT_FormServices;
    }

    public void update(ETT_FormServices ETT_FormServices) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
//        ResultSet rs = null;
        String strSQL = " update sdp_services a set  a.sdp_service_id = ?, a.service_name = ?, a.created_time = to_date(?,'dd/mm/yyyy hh24:mi:ss'), "
                + " a.effective_time = to_date(?,'dd/mm/yyyy hh24:mi:ss'), a.expiry_time = to_date(?,'dd/mm/yyyy hh24:mi:ss'), a.service_number = ?, a.status = ? "
                + " where a.service_id = ? and a.system_id = ? ";
        try {
//            open();
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, ETT_FormServices.getSER_CODE());
            stmt.setString(2, ETT_FormServices.getSER_NAME());
            stmt.setString(3, df.format(ETT_FormServices.getCREATED_TIME()));
            stmt.setString(4, df.format(ETT_FormServices.getEFFECTIVE_TIME()));
            stmt.setString(5, df.format(ETT_FormServices.getEXPIRY_TIME()));
            stmt.setString(6, ETT_FormServices.getSER_NUMBER());
            stmt.setString(7, ETT_FormServices.getSTATUS());
            stmt.setString(8, ETT_FormServices.getSER_ID());
            stmt.setString(9, ETT_FormServices.getSYS_ID());
            stmt.execute();
            stmt.close();
            //
//            strSQL = " insert into sdp_system_content_mode (system_id, content_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < ContentMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, ContentMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
////            cn.setAutoCommit(true);
//            close(stmt);
//            /////////INSERT CHARGING MODE
//            strSQL = " insert into sdp_system_charging_mode (system_id, charging_mode) values(?,?) ";
//            cn.setAutoCommit(false);
//            stmt = cn.prepareStatement(strSQL);
//            for (int i = 0; i < CharMode.size(); i++) {
//                stmt.setString(1, SysId);
//                stmt.setString(2, CharMode.get(i));
//                stmt.addBatch();
//                stmt.executeBatch();
//                cn.commit();
//            }
//            cn.setAutoCommit(true);
//            close(stmt);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            Database.closeObject(cn);
        }
    }

    public boolean delete(ETT_FormServices ETT_FormServices) throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;
        String strSQL =
                " delete from sdp_services a where a.service_id = ? and a.system_id = ? ";
        try {
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            stmt.setString(1, ETT_FormServices.getSER_ID());
            stmt.setString(2, ETT_FormServices.getSYS_ID());
            stmt.executeUpdate();
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            Database.closeObject(stmt);
            Database.closeObject(cn);
        }

    }

    /*    public void remove() throws Exception {
     Connection cn = null;
     PreparedStatement stmt = null;
     ResultSet rs = null;
     String strSQL = " delete from sdp_system_charging_mode where charging_mode = ? and system_id = ? ";
     try {
     //            open();
     cn = AppServer.getConnection();
     stmt = cn.prepareStatement(strSQL);
     for (int i = 0; i < CharMode.size(); i++) {
     stmt.setString(1, CharMode.get(i));
     stmt.setString(2, SysId);
     stmt.execute();
     }
     stmt.close();
     ///
     strSQL = " delete from sdp_system_content_mode where content_mode = ? and system_id = ? ";
     cn.setAutoCommit(false);
     stmt = cn.prepareStatement(strSQL);
     for (int i = 0; i < ContentMode.size(); i++) {
     stmt.setString(1, ContentMode.get(i));
     stmt.setString(2, SysId);
     stmt.addBatch();
     stmt.executeBatch();
     cn.commit();
     }
     cn.setAutoCommit(true);
     stmt.close();
     } finally {
     //            close(rs);
     //            close();
     }
     } */
}
